﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.IO;

namespace Common.DLR
{
    /// <summary>
    /// 
    /// </summary>
    public enum DataPipeSourceType
    {
        /// <summary></summary>
        FileProvider=0,
        /// <summary></summary>
        RowProvider=1,
        /// <summary></summary>
        TableProvider=2
    }

    /// <summary>
    /// 
    /// </summary>
    [Serializable]
    public class DataPipeProvider
    {
        #region fields
        private DataPipeSourceType _SourceType;
        private IEnumerator _Collection;
        private Type _ItemType;
        ///<summary>
        ///</summary>
        public Type ItemType
        {
            get
            {
                return this._ItemType;
            }
        }

        ///<summary>
        ///</summary>
        public IEnumerator Collection
        {
            get
            {
                return this._Collection;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public DataPipeSourceType SourceType
        {
            get
            {
                return this._SourceType;
            }
        }
        #endregion

        #region ctor
        ///<summary>
        ///</summary>
        ///<param name="dbProvider"></param>
        ///<param name="connStr"></param>
        ///<param name="selectSql"></param>
        ///<param name="groupByField"></param>
        ///<param name="sortByField"></param>
        public DataPipeProvider(DataProviderType dbProvider, string connStr, string selectSql, string groupByField, string sortByField)
        {
            if(string.IsNullOrEmpty(groupByField))
            {
                throw new Exception("must specify group by field");
            }

            if(selectSql.ToLower().IndexOf("group by")<0)
            {
                selectSql += " group by " + groupByField;
            }
            if((!string.IsNullOrEmpty(sortByField)) && selectSql.ToLower().IndexOf("order by")<0)
            {
                selectSql += " order by " + sortByField;
            }
            IDbConnection conn = null;
            if (dbProvider == DataProviderType.Oracle)
            {
                conn = new OracleConnection(connStr);
            }
            else
            {
                conn = new SqlConnection(connStr);
            }
            try
            {
                List<DataTable> collection = null;
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = selectSql;
                DataSet dsResult=new DataSet();
                if(dbProvider==DataProviderType.Oracle)
                {
                    OracleDataAdapter daOracle=new OracleDataAdapter((OracleCommand)cmd);
                    daOracle.Fill(dsResult);
                }
                else
                {
                    SqlDataAdapter daSql=new SqlDataAdapter((SqlCommand)cmd);
                    daSql.Fill(dsResult);
                }
                DataTable dtResult = dsResult.Tables[0];
                if (dtResult != null && dtResult.Rows.Count > 0)
                {
                    collection = new List<DataTable>();
                    this._ItemType = typeof (DataTable);
                    this._SourceType = DataPipeSourceType.TableProvider;
                    Dictionary<string, DataTable> tableDataByGroupField = new Dictionary<string, DataTable>();
                    for (int i = 0; i < dtResult.Rows.Count; i++)
                    {
                        if (dtResult.Columns.Contains(groupByField) && dtResult.Rows[i][groupByField] != null &&
                            dtResult.Rows[i][groupByField] != DBNull.Value)
                        {
                            string groupByFieldValue = dtResult.Rows[i][groupByField].ToString();
                            if (tableDataByGroupField.ContainsKey(groupByFieldValue))
                            {
                                DataTable tableData = tableDataByGroupField[groupByFieldValue];
                                DataRow drNew = tableData.NewRow();
                                drNew.ItemArray = dtResult.Rows[i].ItemArray;
                                tableData.Rows.Add(drNew);
                                tableDataByGroupField[groupByFieldValue] = tableData;
                            }
                            else
                            {
                                DataTable tableData = dtResult.Clone();
                                DataRow drNew = tableData.NewRow();
                                drNew.ItemArray = dtResult.Rows[i].ItemArray;
                                tableData.Rows.Add(drNew);
                                tableDataByGroupField.Add(groupByFieldValue, tableData);
                            }
                        }
                        else
                        {
                            throw new Exception("fields returned from query must contain " + groupByField);
                        }
                    }
                    if (tableDataByGroupField.Count > 0)
                    {
                        string[] keys = new string[tableDataByGroupField.Count];
                        tableDataByGroupField.Keys.CopyTo(keys, 0);
                        List<string> keyList = new List<string>();
                        keyList.AddRange(keys);
                        keyList.Sort();
                        foreach (string key in keyList)
                        {
                            collection.Add(tableDataByGroupField[key]);
                        }

                        this._Collection = collection.GetEnumerator();
                    }
                }
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// ctor
        /// </summary>
        /// <param name="dbProvider"></param>
        /// <param name="connStr"></param>
        /// <param name="selectSql"></param>
        /// <param name="sortByField"></param>
        public DataPipeProvider(DataProviderType dbProvider, string connStr, string selectSql, string sortByField)
        {
            if ((!string.IsNullOrEmpty(sortByField)) && selectSql.ToLower().IndexOf("order by") < 0)
            {
                selectSql += " order by " + sortByField;
            }
            IDbConnection conn = null;
            if (dbProvider == DataProviderType.Oracle)
            {
                conn = new OracleConnection(connStr);
            }
            else
            {
                conn = new SqlConnection(connStr);
            }
            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = selectSql;
                DataSet dsResult = new DataSet();
                if (dbProvider == DataProviderType.Oracle)
                {
                    OracleDataAdapter daOracle = new OracleDataAdapter((OracleCommand)cmd);
                    daOracle.Fill(dsResult);
                }
                else
                {
                    SqlDataAdapter daSql = new SqlDataAdapter((SqlCommand)cmd);
                    daSql.Fill(dsResult);
                }
                DataTable dtResult = dsResult.Tables[0];
                List<DataRow> rows = new List<DataRow>();
                this._ItemType = typeof(DataRow);
                this._SourceType = DataPipeSourceType.RowProvider;
                if (dtResult != null && dtResult.Rows.Count > 0)
                {
                    foreach (DataRow dr in dtResult.Rows)
                    {
                        rows.Add(dr);
                    }
                }
                this._Collection = rows.GetEnumerator();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        ///<summary>
        ///</summary>
        ///<param name="dbProvider"></param>
        ///<param name="connStr"></param>
        ///<param name="tblName"></param>
        public DataPipeProvider(DataProviderType dbProvider, string connStr, string tblName)
        {
            IDbConnection conn = null;
            if (dbProvider == DataProviderType.Oracle)
            {
                conn = new OracleConnection(connStr);
            }
            else
            {
                conn = new SqlConnection(connStr);
            }
            try
            {
                conn.Open();
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "select * from " + tblName;
                DataSet dsResult=new DataSet();
                if(dbProvider==DataProviderType.Oracle)
                {
                    OracleDataAdapter daOracle=new OracleDataAdapter((OracleCommand)cmd);
                    daOracle.Fill(dsResult);
                }
                else
                {
                    SqlDataAdapter daSql=new SqlDataAdapter((SqlCommand)cmd);
                    daSql.Fill(dsResult);
                }
                DataTable dtResult = dsResult.Tables[0];
                List<DataRow> rows = new List<DataRow>();
                this._ItemType = typeof (DataRow);
                this._SourceType = DataPipeSourceType.RowProvider;
                if(dtResult !=null && dtResult.Rows.Count>0)
                {
                    foreach(DataRow dr in dtResult.Rows)
                    {
                        rows.Add(dr);
                    }
                }
                this._Collection = rows.GetEnumerator();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="folderPath"></param>
        /// <param name="filter"></param>
        /// <param name="searchOp"></param>
        public DataPipeProvider(string folderPath, string filter, SearchOption searchOp)
        {
            DirectoryInfo dirInfo=new DirectoryInfo(folderPath);
            FileInfo[] files = dirInfo.GetFiles(filter, searchOp);
            this._ItemType = typeof (FileInfo);
            this._SourceType = DataPipeSourceType.FileProvider;
            this._Collection = files.GetEnumerator();
        }
        #endregion
    }
}
